import pymysql
#from ConnectSql 
# import Ui_MainWindow
import sys
from PyQt5.QtWidgets import QMainWindow, QApplication, QTableWidgetItem, QMessageBox
from PyQt5 import QtWidgets, QtCore, QtGui

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(714, 302)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.name_edit = QtWidgets.QLineEdit(self.centralwidget)
        self.name_edit.setGeometry(QtCore.QRect(110, 80, 131, 31))
        self.name_edit.setObjectName("name_edit")
        self.username_edit = QtWidgets.QLineEdit(self.centralwidget)
        self.username_edit.setGeometry(QtCore.QRect(110, 120, 131, 31))
        self.username_edit.setObjectName("username_edit")
        self.username_label = QtWidgets.QLabel(self.centralwidget)
        self.username_label.setGeometry(QtCore.QRect(80, 130, 60, 16))
        self.username_label.setObjectName("username_label")
        self.name_labe = QtWidgets.QLabel(self.centralwidget)
        self.name_labe.setGeometry(QtCore.QRect(80, 90, 60, 16))
        self.name_labe.setObjectName("name_labe")
        self.insert_btn = QtWidgets.QPushButton(self.centralwidget)
        self.insert_btn.setGeometry(QtCore.QRect(70, 210, 111, 41))
        self.insert_btn.setObjectName("insert_btn")
        self.del_btn = QtWidgets.QPushButton(self.centralwidget)
        self.del_btn.setGeometry(QtCore.QRect(180, 210, 111, 41))
        self.del_btn.setObjectName("del_btn")
        self.password_label = QtWidgets.QLabel(self.centralwidget)
        self.password_label.setGeometry(QtCore.QRect(80, 170, 60, 16))
        self.password_label.setObjectName("password_label")
        self.password_edit = QtWidgets.QLineEdit(self.centralwidget)
        self.password_edit.setGeometry(QtCore.QRect(110, 160, 131, 31))
        self.password_edit.setObjectName("password_edit")
        self.select_btn = QtWidgets.QPushButton(self.centralwidget)
        self.select_btn.setGeometry(QtCore.QRect(405, 210, 113, 41))
        self.select_btn.setObjectName("select_btn")
        self.update_btn = QtWidgets.QPushButton(self.centralwidget)
        self.update_btn.setGeometry(QtCore.QRect(290, 210, 113, 41))
        self.update_btn.setObjectName("update_btn")
        self.data_table = QtWidgets.QTableWidget(self.centralwidget)
        self.data_table.setGeometry(QtCore.QRect(260, 80, 371, 111))
        self.data_table.setObjectName("data_table")
        self.data_table.setColumnCount(3)
        self.title_label = QtWidgets.QLabel(self.centralwidget)
        self.title_label.setGeometry(QtCore.QRect(240, 20, 241, 31))
        font = QtGui.QFont()
        font.setPointSize(24)
        self.title_label.setFont(font)
        self.title_label.setTextFormat(QtCore.Qt.AutoText)
        self.title_label.setObjectName("title_label")
        self.result_label = QtWidgets.QLabel(self.centralwidget)
        self.result_label.setGeometry(QtCore.QRect(550, 220, 100, 16))
        self.result_label.setObjectName("result_labe")
        MainWindow.setCentralWidget(self.centralwidget)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)
 
        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)
 
    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.username_label.setText(_translate("MainWindow", "账号"))
        self.name_labe.setText(_translate("MainWindow", "姓名"))
        self.insert_btn.setText(_translate("MainWindow", "新增数据"))
        self.del_btn.setText(_translate("MainWindow", "删除数据"))
        self.password_label.setText(_translate("MainWindow", "密码"))
        self.select_btn.setText(_translate("MainWindow", "查询数据"))
        self.update_btn.setText(_translate("MainWindow", "修改数据"))
        self.title_label.setText(_translate("MainWindow", "MySql数据库增删改查"))
        self.result_label.setText(_translate("MainWindow", "数据返回提示！"))




 
class MainWindow(QMainWindow,Ui_MainWindow):
    def __init__(self):
        super(MainWindow, self).__init__()
        self.setupUi(self)
        # 定义表名
        self.table_name = 'users' 
 
        # 设置数据库连接
        self.connection = pymysql.connect(
            host='localhost',  # MySQL 服务器主机名
            port=3306,    #端口
            user='root',  # 数据库用户名
            password='123456',  # 数据库密码
            database='database_name',  # 数据库名
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
 
        # 初始化表格视图
        self.init_table_view()
 
 
        # 连接按钮点击事件
        self.insert_btn.clicked.connect(self.insert_data)
        self.del_btn.clicked.connect(self.delete_data)
        self.select_btn.clicked.connect(self.select_data)
        self.update_btn.clicked.connect(self.update_data)

    # 清除输入框内容
    def clearinput(self):
        self.name_edit.clear()
        self.username_edit.clear()
        self.password_edit.clear()
        
    #输入框清除
    def init_table_view(self):
        try:
            # 检查连接是否存在，断开的话重连
            self.connection.ping(reconnect=True)
            # 执行查询
            with self.connection.cursor() as cursor:
                sql_query = f"SELECT name, username, password FROM {self.table_name}"
                cursor.execute(sql_query)
                results = cursor.fetchall()
 
                if len(results) == 0:
                    self.data_table.setRowCount(0)
                    QMessageBox.information(self, "提示", "没有符合条件的记录", QMessageBox.Ok)
                else:
                    self.data_table.setRowCount(len(results))
                    self.data_table.setColumnCount(3)
 
                    for index, result in enumerate(results):
                        name_item = QTableWidgetItem()
                        name_item.setText(str(result['name']))
 
                        username_item = QTableWidgetItem()
                        username_item.setText(str(result['username']))
 
                        password_item = QTableWidgetItem()
                        password_item.setText(str(result['password']))
 
                        self.data_table.setItem(index, 0, name_item)
                        self.data_table.setItem(index, 1, username_item)
                        self.data_table.setItem(index, 2, password_item)
 
                    # 设置表头
                    self.data_table.setHorizontalHeaderLabels(['姓名', '账号', '密码'])
                    self.result_label.setText("数据加载成功！")
 
        except Exception as e:
            # print(e)
            self.result_label.setText("运行错误！")
 
    def execute_query(self, sql_query):
        with self.connection.cursor() as cursor:
            cursor.execute(sql_query)
            return cursor.fetchall()
 
    def insert_data(self):
        # 获取输入框的值
        name = self.name_edit.text()
        username = self.username_edit.text()
        password = self.password_edit.text()
        # 插入数据
        sql_query = f"INSERT INTO {self.table_name} (name, username, password) VALUES ('{name}', '{username}', '{password}')"
        self.execute_query(sql_query)
        self.connection.commit()
        self.init_table_view()
        self.clearinput()
        self.result_label.setText("新增数据成功！")
 
    def delete_data(self):
        username = self.username_edit.text()
        # 删除数据
        sql_query = f"DELETE FROM {self.table_name} WHERE username = {username}"
        self.execute_query(sql_query)
        self.connection.commit()
        self.init_table_view()
        self.result_label.setText("删除数据成功！")
 
    def select_data(self):
        username = self.username_edit.text()
        try:
            # 检查连接是否存在，断开的话重连
            self.connection.ping(reconnect=True)
            if username=='':
                self.init_table_view()
                self.result_label.setText("请输入账号查询！")
                return
            # 执行查询
            with self.connection.cursor() as cursor:
                sql_query = f"SELECT name, username, password FROM {self.table_name} WHERE username={username}"
                cursor.execute(sql_query)
                results = cursor.fetchall()
 
                if len(results) == 0:
                    self.init_table_view()
                    QMessageBox.information(self, "提示", "没有符合条件的记录", QMessageBox.Ok)
                else:
                    self.data_table.clear()
                    self.data_table.setRowCount(len(results))
                    self.data_table.setColumnCount(3)
 
                    for index, result in enumerate(results):
                        name_item = QTableWidgetItem()
                        name_item.setText(str(result['name']))
 
                        username_item = QTableWidgetItem()
                        username_item.setText(str(result['username']))
 
                        password_item = QTableWidgetItem()
                        password_item.setText(str(result['password']))
 
                        self.data_table.setItem(index, 0, name_item)
                        self.data_table.setItem(index, 1, username_item)
                        self.data_table.setItem(index, 2, password_item)
 
                    # 设置表头
                    self.data_table.setHorizontalHeaderLabels(['姓名', '账号', '密码'])
                    self.result_label.setText("数据加载成功！")
 
        except Exception as e:
            print(e)
            self.result_label.setText("运行错误！")
 
    def update_data(self):
        # 获取修改后的值
        new_name = self.name_edit.text()
        username = self.username_edit.text()
        new_password = self.password_edit.text()
        # 更新数据
        sql_query = f"UPDATE {self.table_name} SET name='{new_name}', password='{new_password}' WHERE username='{username}'"
        self.execute_query(sql_query)
        self.connection.commit()
        self.init_table_view()
        self.clearinput()
        self.result_label.setText("修改数据成功！")
 
 
if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = MainWindow()
    window.show()
    sys.exit(app.exec_())

